#!/usr/local/bin/python
'''
Created on 2012/05/05

@author: Ryu Naeki
'''

import MySQLdb
import datetime

class DaoTweet(object):
    '''
    classdocs
    '''

    USERNAME = "hack1"
    PASSWORD = "hack1"
    DBNAME = "gake-hack"
    CHARSET = "utf8"

    __db = None;
    
    def __init__(self, username, password, dbname, charset):
        '''
        Constructor
        '''
        self.__db = MySQLdb.connect(user=username, passwd=password, db=dbname, charset=charset);
        
    def add_tweet(self, tweet_id, created_at, from_user, text, location_latitude, location_longitude, image_url, image_id):
        query = u"INSERT INTO tweet_table(tweet_id, created_at, from_user, text, location_latitude, location_longitude, image_url, image_id) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (tweet_id, created_at, from_user, text, location_latitude, location_longitude, image_url, image_id)
        c = self.__db.cursor()
        c.execute(query)
        self.__db.commit()
        
    def get_tweet(self):
        query = u"SELECT tt.tweet_id, created_at, from_user, text, location_latitude, location_longitude, image_url, image_id, like_count FROM tweet_table AS tt LEFT OUTER JOIN like_count_table AS lct ON tt.tweet_id = lct.tweet_id"
        c = self.__db.cursor()
        c.execute(query)       
        rows = c.fetchall()
        
        items = []
        for row in rows:
            item = dict()
            item["tweet_id"] = str(row[0])
            item["created_at"] = row[1].strftime('%Y/%m/%d %H:%M:%S')
            item["from_user"] = row[2]
            item["text"] = row[3]
            item["location_latitude"] = str(row[4])
            item["location_longitude"] = str(row[5])
            item["image_url"] = row[6]
            item["image_id"] = row[7]
            cnt = 0
            if row[8] != None:
                cnt = row[8]
            item["like_count"] = str(cnt)
            items.append(item)
        return items
    
    def like_exists(self, like_user, tweet_id):
        query = u"SELECT like_id FROM like_table WHERE like_user='%s' AND tweet_id='%s' LIMIT 1" % (like_user, tweet_id)
        c = self.__db.cursor()
        c.execute(query)       
        rows = c.fetchall() 
        if len(rows) > 0:
            return True
        return False
    
    def add_like(self, like_at, like_user, tweet_id):
        query = u"INSERT INTO like_table(like_at, like_user, tweet_id) VALUES ('%s', '%s', '%s')" % (like_at, like_user, tweet_id)
        c = self.__db.cursor()
        c.execute(query)
        self.__db.commit()
        
    def count_like(self, tweet_id):
        query = u"SELECT COUNT(like_id) FROM like_table WHERE tweet_id='%s' LIMIT 1" % tweet_id
        c = self.__db.cursor()
        c.execute(query)       
        row = c.fetchone()[0]
        return int(row)
    
    def set_like_count(self, tweet_id, count):
        query = u"INSERT INTO like_count_table(tweet_id, like_count) VALUES ('%s', '%s') ON DUPLICATE KEY UPDATE like_count='%s'" % (tweet_id, count, count)
        c = self.__db.cursor()
        c.execute(query)
        self.__db.commit()
    
    def flush_like_count(self, tweet_id):
        count = self.count_like(tweet_id)
        self.set_like_count(tweet_id, count)
       
if __name__ == "__main__":
    '''
    STR_CREATE_TIME = "Mon, 30 Apr 2012 02:35:52 +0000"
    
    tweet = DaoTweet(DaoTweet.USERNAME, DaoTweet.PASSWORD, "gake-hack", "utf8")
    create_time = datetime.datetime.strptime(STR_CREATE_TIME, "%a, %d %b %Y %H:%M:%S +0000")
    tweet.add_tweet(196789940082851841, create_time, "gaketest", "\u30c6\u30b9\u30c8 http:\/\/t.co\/GicLXVug #hackearthquakes", 36.7412, 139.6793, "http:\/\/twitpic.com\/9ffzj1", "9ffzj1")
    '''
    pass

